#!/bin/bash

APP=edu
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

# 1.1.1各来源流量统计
ads_traffic_stats_by_channel="
insert overwrite table ${APP}.ads_traffic_stats_by_channel
select * from ${APP}.ads_traffic_stats_by_channel
union
select
    '$do_date' dt,
    recent_days,
    channel,
    cast(count(distinct(mid_id)) as bigint) uv_count,
    cast(avg(during_time_1d)/1000 as bigint) avg_duration_sec,
    cast(avg(page_count_1d) as bigint) avg_page_count,
    cast(count(*) as bigint) sv_count,
    cast(sum(if(page_count_1d=1,1,0))/count(*) as decimal(16,2)) bounce_rate
from ${APP}.dws_traffic_session_page_view_1d lateral view explode(array(1,7,30)) tmp as recent_days
where dt>=date_add('$do_date',-recent_days+1)
and dt<='$do_date'
group by recent_days,channel;
"
# 1.1.2路径分析
ads_page_path="
insert overwrite table ${APP}.ads_page_path
select * from ${APP}.ads_page_path
union
select
    '$do_date' dt,
    recent_days,
    source,
    nvl(target,'null'),
    count(*) path_count
from
(
    select
        recent_days,
        concat('step-',rn,':',page_id) source,
        concat('step-',rn+1,':',next_page_id) target
    from
    (
        select
            dt,
            recent_days,
            session_id,
            page_id,
            lead(page_id,1,null) over(partition by session_id,recent_days order by view_time) next_page_id,
            row_number() over (partition by session_id,recent_days order by view_time) rn
        from ${APP}.dwd_traffic_page_view_inc lateral view explode(array(1,7,30)) tmp as recent_days
        where dt>=date_add('$do_date',-recent_days+1)
        and dt <= '$do_date'
    )t1
)t2
group by recent_days,source,target;
"
# 1.1.3 各来源下单统计
ads_sc_order_stats="
insert overwrite table ${APP}.ads_sc_order_stats
select *from ${APP}.ads_sc_order_stats
union
select
    '$do_date' dt,
    t6.sc,
    t6.recent_days,
    t6.order_total_amount,
    cast(t6.user_order_num/t7.sc_user_num as decimal(16,2)) vate
from (
         select t2.sc,
                1                          recent_days,
                sum(order_total_amount_1d) order_total_amount,
                count(distinct (user_id))  user_order_num
         from (
                  select user_id,
                         order_total_amount_1d
                  from ${APP}.dws_trade_user_order_1d
                  where dt = '$do_date'
              ) t1
                  left join
              (
                  select common.uid,
                         common.sc
                  from ${APP}.ods_log_inc
                  where dt = '$do_date'
              ) t2
              on t1.user_id = t2.uid
         group by t2.sc
         union all
         select t4.sc,
                t3.recent_days,
                sum(if(t3.recent_days = 7, order_total_amount_7d, order_total_amount_30d)) order_total_amount,
                count(distinct (user_id))                                                  sc_user_order_num
         from (
                  select recent_days,
                         user_id,
                         order_total_amount_7d,
                         order_total_amount_30d
                  from ${APP}.dws_trade_user_order_nd
                           lateral view explode(array(7, 30)) tmp as recent_days
                  where dt = '$do_date'
              ) t3
                  left join
              (
                  select recent_days,
                         common.uid,
                         common.sc
                  from ${APP}.ods_log_inc
                           lateral view explode(array(7, 30)) tmp as recent_days
                  where dt <= '$do_date'
                    and dt >= date_add('$do_date', -recent_days + 1)
              ) t4
              on t3.user_id = t4.uid
                  and t3.recent_days = t4.recent_days
         group by t4.sc, t3.recent_days
     )t6
join
     (
         select t5.recent_days,
                t5.sc,
                count(distinct (t5.uid)) sc_user_num
         from (
                  select recent_days,
                         common.uid,
                         common.sc
                  from ${APP}.ods_log_inc
                           lateral view explode(array(1, 7, 30)) tmp as recent_days
                  where dt <= '$do_date'
                    and dt >= date_add('$do_date', -recent_days + 1)
              ) t5
         group by t5.recent_days, t5.sc
     )t7
on t6.recent_days=t7.recent_days
and t6.sc=t7.sc;
"
# 1.2.1用户变动统计
ads_user_change="
insert overwrite table ${APP}.ads_user_change
select * from ${APP}.ads_user_change
union
select
    churn.dt,
    user_churn_count,
    user_back_count
from
(
    select
        '$do_date' dt,
        count(*) user_churn_count
    from ${APP}.dws_user_user_login_td
    where dt='$do_date'
    and login_date_last=date_add('$do_date',-7)
)churn
join
(
    select
        '$do_date' dt,
        count(*) user_back_count
    from
    (
        select
            user_id,
            login_date_last
        from ${APP}.dws_user_user_login_td
        where dt='$do_date'
  and login_date_last='$do_date'   
    )t1
    join
    (
        select
            user_id,
            login_date_last login_date_previous
        from ${APP}.dws_user_user_login_td
        where dt=date_add('$do_date',-1) 
    )t2
    on t1.user_id=t2.user_id
    where datediff(login_date_last,login_date_previous)>=8
)back
on churn.dt=back.dt;
"
# 1.2.2用户留存率
ads_user_retention="
insert overwrite table ${APP}.ads_user_retention
select * from ${APP}.ads_user_retention
union
select
    '$do_date' dt,
    login_date_first create_date,
    datediff('$do_date',login_date_first) retention_day,
    sum(if(login_date_last='$do_date',1,0)) retention_count,
    count(*) new_user_count,
    cast(sum(if(login_date_last='$do_date',1,0))/count(*)*100 as decimal(16,2)) retention_rate
from
(
    select
        user_id,
        date_id login_date_first
    from ${APP}.dwd_user_register_inc
    where dt>=date_add('$do_date',-7)
    and dt<'$do_date'
)t1
join
(
    select
        user_id,
        login_date_last
    from ${APP}.dws_user_user_login_td
    where dt='$do_date'
)t2
on t1.user_id=t2.user_id
group by login_date_first;
"
# 1.2.3用户新增活跃统计
ads_user_stats="
insert overwrite table ${APP}.ads_user_stats
select * from ${APP}.ads_user_stats
union
select
    '$do_date' dt,
    new.recent_days,
    new.new_user_count,
    act.active_user_count
from
(
    select
        recent_days,
        count(*) new_user_count
    from ${APP}.dwd_user_register_inc
    lateral view explode(array(1,7,30)) tmp as recent_days
    where dt>=date_add('$do_date',-recent_days+1)
    and dt<='$do_date'
    group by recent_days
)new
join
(
    select
        recent_days,
        count(*) active_user_count
    from ${APP}.dws_user_user_login_td
    lateral view explode(array(1,7,30)) tmp as recent_days
    where dt='$do_date'
    and login_date_last>=date_add('$do_date',-recent_days+1)
    and login_date_last<='$do_date'
    group by recent_days
)act
on new.recent_days=act.recent_days;
"
# 1.2.4 用户行为漏斗分析
ads_user_action="
with
tmp_hg as
(
    select
        1 recent_days,
        sum(if(page_id='home',1,0)) home_count,
        sum(if(page_id='course_detail',1,0)) course_detail_count
    from ${APP}.dws_traffic_page_visitor_page_view_1d
    where dt='$do_date'
    and page_id in ('home','course_detail')
    union all
    select
        recent_days,
        sum(if(page_id='home' and if(recent_days=7,view_count_7d,view_count_30d)>0,1,0)) home_count,
        sum(if(page_id='course_detail' and if(recent_days=7,view_count_7d,view_count_30d)>0,1,0)) course_detail_count
    from ${APP}.dws_traffic_page_visitor_page_view_nd
    lateral view explode(array(7,30)) tmp as recent_days
    where dt='$do_date'
    and page_id in ('home','course_detail')
    group by recent_days
),
tmp_cart as
(
    select
        1 recent_days,
        count(*) cart_count
    from ${APP}.dws_trade_user_cart_add_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        sum(if(if(recent_days=7,cart_add_count_7d,cart_add_count_30d)>0,1,0)) cart_count
    from ${APP}.dws_trade_user_cart_add_nd
    lateral view explode(array(7,30)) tmp as recent_days
    where dt='$do_date'
    group by recent_days
),
tmp_order as
(
    select
        1 recent_days,
        count(*) order_count
    from ${APP}.dws_trade_user_order_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        sum(if(if(recent_days=7,order_count_7d,order_count_30d)>0,1,0)) order_count
    from ${APP}.dws_trade_user_order_nd
    lateral view explode(array(7,30)) tmp as recent_days
    where dt='$do_date'
    group by recent_days
),
tmp_pay as
(
    select
        1 recent_days,
        count(*) payment_count
    from ${APP}.dws_trade_user_payment_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        sum(if(if(recent_days=7,payment_count_7d,payment_count_30d)>0,1,0)) payment_count
    from ${APP}.dws_trade_user_payment_nd
    lateral view explode(array(7,30)) tmp as recent_days
    where dt='$do_date'
    group by recent_days
)
insert overwrite table ${APP}.ads_user_action
select * from ${APP}.ads_user_action
union
select
    '$do_date' dt,
    tmp_hg.recent_days,
    tmp_hg.home_count,
    tmp_hg.course_detail_count,
    tmp_cart.cart_count,
    tmp_order.order_count,
    tmp_pay.payment_count
from tmp_hg
join tmp_cart on tmp_hg.recent_days=tmp_cart.recent_days
join tmp_order on tmp_hg.recent_days=tmp_order.recent_days
join tmp_pay on tmp_hg.recent_days=tmp_pay.recent_days;
"
# 1.2.5 新增交易用户统计
ads_new_buyer_stats="
insert overwrite table ${APP}.ads_new_buyer_stats
select * from ${APP}.ads_new_buyer_stats
union
select
    '$do_date' dt,
    od.recent_days,
    new_order_user_count,
    new_payment_user_count
from
(
    select
        recent_days,
        sum(if(order_date_first>=date_add('$do_date',-tmp.recent_days+1),1,0)) new_order_user_count
    from ${APP}.dws_trade_user_order_td
    lateral view explode(array(1,7,30)) tmp as recent_days
    where dt='$do_date'
    group by tmp.recent_days
)od
join
(
    select
        recent_days,
        sum(if(payment_date_first>=date_add('$do_date',-tmp.recent_days+1),1,0)) new_payment_user_count
    from ${APP}.dws_trade_user_payment_td
    lateral view explode(array(1,7,30)) tmp as recent_days
    where dt='$do_date'
    group by tmp.recent_days
)pay
on od.recent_days=pay.recent_days;
"
# 1.2.6 各年龄段下单用户数
ads_age_range_order_stats="
insert overwrite table ${APP}.ads_age_range_order_stats
select * from ${APP}.ads_age_range_order_stats
union
select
    '$do_date' dt,
    1 recent_days,
    user_age_range,
    count(*) order_user_count
from
(
    select
        user_id
    from ${APP}.dws_trade_user_order_1d
    where dt='$do_date'
) uo
left join
(
    select
        id,
        case
            when year(birthday) >= 2000 then '00后'
            when year(birthday) >= 1990 and year(birthday) < 2000 then '90后'
            when year(birthday) >= 1980 and year(birthday) < 1990 then '80后'
            when year(birthday) >= 1970 and year(birthday) < 1980 then '70后'
            else '60后及以下' end user_age_range
    from ${APP}.dim_user_zip
    where dt='9999-12-31'
) uz
on uo.user_id=uz.id
group by user_age_range
union
select
    '$do_date' dt,
    recent_days,
    user_age_range,
    sum(if(recent_days=7 and order_count_7d=0,0,1)) order_user_count
from
(
    select
        recent_days,
        user_id,
        order_count_7d,
        order_count_30d
    from ${APP}.dws_trade_user_order_nd
    lateral view explode(array(7,30)) tmp as recent_days
    where dt='$do_date'
) uo
left join
(
    select
        id,
        case
            when year(birthday) >= 2000 then '00后'
            when year(birthday) >= 1990 and year(birthday) < 2000 then '90后'
            when year(birthday) >= 1980 and year(birthday) < 1990 then '80后'
            when year(birthday) >= 1970 and year(birthday) < 1980 then '70后'
            else '60后及以下' end user_age_range
    from ${APP}.dim_user_zip
    where dt='9999-12-31'
) uz
on uo.user_id=uz.id
group by recent_days,user_age_range;
"
# 1.3.1 各分类课程交易统计
ads_trade_stats_by_cate="
insert overwrite table ${APP}.ads_trade_stats_by_cate
select * from ${APP}.ads_trade_stats_by_cate
union
select
    '$do_date' dt,
    recent_days,
    category_id,
    category_name,
    order_count,
    order_user_count,
    order_total_amount
from
(
    select
        1 recent_days,
        category_id,
        category_name,
        sum(order_count_1d) order_count,
        count(distinct(if(order_count_1d>0,user_id,null))) order_user_count,
        sum(order_total_amount_1d) order_total_amount
    from ${APP}.dws_trade_user_course_order_1d
    where dt='$do_date'
    group by category_id,category_name
    union all
    select
        7 recent_days,
        category_id,
        category_name,
        sum(order_count_1d) order_count,
        count(distinct(if(order_count_1d>0,user_id,null))) order_user_count,
        sum(order_total_amount_1d) order_total_amount
    from ${APP}.dws_trade_user_course_order_1d
    where dt>=date_add('$do_date',-6)
    and dt<='$do_date'
    group by category_id,category_name
    union all
    select
        30 recent_days,
        category_id,
        category_name,
        sum(order_count_1d) order_count,
        count(distinct(if(order_count_1d>0,user_id,null))) order_user_count,
        sum(order_total_amount_1d) order_total_amount
    from ${APP}.dws_trade_user_course_order_1d
    where dt>=date_add('$do_date',-29)
    and dt<='$do_date'   
    group by category_id,category_name
)odr;
"
# 1.3.2 各学科课程交易统计
ads_trade_stats_by_sub="
insert overwrite table ${APP}.ads_trade_stats_by_sub
select * from ${APP}.ads_trade_stats_by_sub
union
select
    '$do_date' dt,
    recent_days,
    subject_id,
    subject_name,
    order_count,
    order_user_count,
    order_total_amount
from
(
    select
        1 recent_days,
        subject_id,
        subject_name,
        sum(order_count_1d) order_count,
        count(distinct(if(order_count_1d>0,user_id,null))) order_user_count,
        sum(order_total_amount_1d) order_total_amount
    from ${APP}.dws_trade_user_course_order_1d
    where dt='$do_date'
    group by subject_id,subject_name
    union all
    select
        7 recent_days,
        subject_id,
        subject_name,
        sum(order_count_1d) order_count,
        count(distinct(if(order_count_1d>0,user_id,null))) order_user_count,
        sum(order_total_amount_1d) order_total_amount
    from ${APP}.dws_trade_user_course_order_1d
    where dt>=date_add('$do_date',-6)
    and dt<='$do_date'
    group by subject_id,subject_name
    union all
    select
        30 recent_days,
        subject_id,
        subject_name,
        sum(order_count_1d) order_count,
        count(distinct(if(order_count_1d>0,user_id,null))) order_user_count,
        sum(order_total_amount_1d) order_total_amount
    from ${APP}.dws_trade_user_course_order_1d
    where dt>=date_add('$do_date',-29)
    and dt<='$do_date'   
    group by subject_id,subject_name
)odr;
"
# 1.3.3 各课程交易统计
ads_trade_stats_by_course="
insert overwrite table ${APP}.ads_trade_stats_by_course
select * from ${APP}.ads_trade_stats_by_course
union
select
    '$do_date' dt,
    recent_days,
    course_id,
    course_name,
    order_count,
    order_user_count,
    order_total_amount
from
(
    select
        1 recent_days,
        course_id,
        course_name,
        sum(order_count_1d) order_count,
        count(distinct(if(order_count_1d>0,user_id,null))) order_user_count,
        sum(order_total_amount_1d) order_total_amount
    from ${APP}.dws_trade_user_course_order_1d
    where dt='$do_date'
    group by course_id,course_name
    union all
    select
        7 recent_days,
        course_id,
        course_name,
        sum(order_count_1d) order_count,
        count(distinct(if(order_count_1d>0,user_id,null))) order_user_count,
        sum(order_total_amount_1d) order_total_amount
    from ${APP}.dws_trade_user_course_order_1d
    where dt>=date_add('$do_date',-6)
    and dt<='$do_date'
    group by course_id,course_name
    union all
    select
        30 recent_days,
        course_id,
        course_name,
        sum(order_count_1d) order_count,
        count(distinct(if(order_count_1d>0,user_id,null))) order_user_count,
        sum(order_total_amount_1d) order_total_amount
    from ${APP}.dws_trade_user_course_order_1d
    where dt>=date_add('$do_date',-29)
    and dt<='$do_date'
    group by course_id,course_name
)odr;
"
# 1.3.4 各课程评价统计
ads_course_review_stats="
insert overwrite table ${APP}.ads_course_review_stats
select * from ${APP}.ads_course_review_stats
union
select
    '$do_date' dt,
    recent_days,
    course_id,
    sum_review_star/review_user_count,
    review_user_count,
    cast(good_review_count as double)/review_user_count * 100
from
(
    select
        tmp.recent_days,
        course_id,
        case
            when tmp.recent_days=1 then sum(if(dt>='$do_date',review_star,0))
            when tmp.recent_days=7 then sum(if(dt>=date_add('$do_date',-6),review_star,0))
            else sum(review_star) end sum_review_star,
        case
            when tmp.recent_days=1 then sum(if(dt>='$do_date',1,0))
            when tmp.recent_days=7 then sum(if(dt>=date_add('$do_date',-6),1,0))
            else sum(1) end review_user_count,
        case
            when tmp.recent_days=1 then sum(if(dt>='$do_date' and review_star=5,1,0))
            when tmp.recent_days=7 then sum(if(dt>=date_add('$do_date',-6)  and review_star=5,1,0))
            else sum(if(review_star=5,1,0)) end good_review_count
    from ${APP}.dwd_interaction_review_inc
    lateral view explode(array(1,7,30)) tmp as recent_days
    where dt>=date_add('$do_date',-29)
    and dt<='$do_date'
    group by tmp.recent_days,course_id
)t1;
"
# 1.3.5 各分类课程试听留存统计
ads_audition_category_retention="
insert overwrite table ${APP}.ads_audition_category_retention
select * from ${APP}.ads_audition_category_retention
union
select
    '$do_date' dt,
    category_id,
    category_name,
    count(*) audition_count,
    sum(if(order_time is not null,1,0)) order_count,
    cast(sum(if(order_time is not null,1,0))*100/count(*) as decimal(16,2))
from ${APP}.dwd_trade_audition_order_full
where dt = '9999-12-31'
or dt >= date_add('$do_date',-7)
group by category_id,category_name;
"
# 1.3.6 各学科试听留存统计
ads_audition_subject_retention="
insert overwrite table ${APP}.ads_audition_subject_retention
select * from ${APP}.ads_audition_subject_retention
union
select
    '$do_date' dt,
    subject_id,
    subject_name,
    count(*) audition_count,
    sum(if(order_time is not null,1,0)) order_count,
    cast(sum(if(order_time is not null,1,0))*100/count(*) as decimal(16,2))
from ${APP}.dwd_trade_audition_order_full
where dt = '9999-12-31'
or dt >= date_add('$do_date',-7)
group by subject_id,subject_name;
"
# 1.3.7 各课程试听留存统计
ads_audition_course_retention="
insert overwrite table ${APP}.ads_audition_course_retention
select * from ${APP}.ads_audition_course_retention
union
select
    '$do_date' dt,
    course_id,
    course_name,
    count(*) audition_count,
    sum(if(order_time is not null,1,0)) order_count,
    cast(sum(if(order_time is not null,1,0))*100/count(*) as decimal(16,2))
from ${APP}.dwd_trade_audition_order_full
where dt = '9999-12-31'
or dt >= date_add('$do_date',-7)
group by course_id,course_name;
"
# 1.4.1 交易综合统计
ads_trade_stats="insert overwrite table ${APP}.ads_trade_stats
select * from ${APP}.ads_trade_stats
union
select
    '$do_date',
    recent_days,
    order_total_amount,
    order_count,
    order_user_count
from
(
    select
        1 recent_days,
        sum(order_total_amount_1d) order_total_amount,
        sum(order_count_1d) order_count,
        count(*) order_user_count
    from ${APP}.dws_trade_user_order_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        sum(order_total_amount),
        sum(order_count),
        sum(if(order_count>0,1,0))
    from
    (
        select
            recent_days,
            case recent_days
                when 7 then order_total_amount_7d
                when 30 then order_total_amount_30d
            end order_total_amount,
            case recent_days
                when 7 then order_count_7d
                when 30 then order_count_30d
            end order_count
        from ${APP}.dws_trade_user_order_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
    )t1
    group by recent_days
)odr;
"
# 1.4.2 各省份交易统计
ads_order_by_province="
insert overwrite table ${APP}.ads_order_by_province
select * from ${APP}.ads_order_by_province
union
select
    dt,
    t1.recent_days,
    t1.province_id,
    province_name,
    iso_code,
    iso_3166_2,
    order_count,
    order_final_amount,
    order_user_count
from
(
    select
       '$do_date' dt,
       1 recent_days,
       province_id,
       province_name,
       iso_code,
       iso_3166_2,
       order_count_1d order_count,
       order_final_amount_1d order_final_amount
    from ${APP}.dws_trade_province_order_1d
    where dt='$do_date'
    union all
    select
       '$do_date' dt,
       recent_days,
       province_id,
       province_name,
       iso_code,
       iso_3166_2,
       if(recent_days=7,order_count_7d,order_count_30d) order_count,
       if(recent_days=7,order_final_amount_7d,order_final_amount_30d) order_final_amount
    from ${APP}.dws_trade_province_order_nd lateral view explode(array(7,30)) tmp as recent_days
    where dt='$do_date'
)t1
full outer join
(    
    select
       1 recent_days,
       province_id,
       count(distinct(if(order_count_1d>0,user_id,null))) order_user_count
    from ${APP}.dws_trade_user_order_1d
    where dt='$do_date'
    group by province_id
    union all
    select
        recent_days,
        province_id,
        case recent_days
            when 7 then count(distinct(if(order_count_7d>0,user_id,null))) 
            when 30 then count(distinct(if(order_count_30d>0,user_id,null)))  
        end order_user_count
    from ${APP}.dws_trade_user_order_nd lateral view explode(array(7,30)) tmp as recent_days
    where dt='$do_date'
    group by recent_days,province_id
)t2
on t1.recent_days=t2.recent_days
and t1.province_id=t2.province_id;
"
# 1.5.1 各试卷相关指标统计
ads_paper="
insert overwrite table ${APP}.ads_paper
select * from ${APP}.ads_paper
union
select
    '$do_date' dt,
    1,
    paper_id,
    user_count_1d,
    cast(sum_score_1d/user_count_1d as string),
    cast(sum_duration_sec_1d/user_count_1d as string)
from ${APP}.dws_interaction_paper_course_test_exam_1d
where dt='$do_date'
union all
select
    '$do_date' dt,
    recent_days,
    paper_id,
    if(recent_days=7,user_count_7d,user_count_30d),
    cast(if(recent_days=7,sum_score_7d,sum_score_30d)/if(recent_days=7,user_count_7d,user_count_30d) as string),
    cast(if(recent_days=7,sum_duration_sec_7d,sum_duration_sec_30d)/if(recent_days=7,user_count_7d,user_count_30d) as string)
from ${APP}.dws_interaction_paper_course_test_exam_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date';
"
# 1.5.2各课程考试相关指标统计
ads_course_exam="
insert overwrite table ${APP}.ads_course_exam
select * from ${APP}.ads_course_exam
union
select
    '$do_date' dt,
    1,
    course_id,
    user_count_1d,
    cast(sum_score_1d/user_count_1d as STRING),
    cast(sum_duration_sec_1d/user_count_1d as STRING)
from ${APP}.dws_interaction_paper_course_test_exam_1d
where dt='$do_date'
union all
select
    '$do_date' dt,
    recent_days,
    course_id,
    if(recent_days=7,user_count_7d,user_count_30d),
    cast(if(recent_days=7,sum_score_7d,sum_score_30d)/if(recent_days=7,user_count_7d,user_count_30d) as STRING),
    cast(if(recent_days=7,sum_duration_sec_7d,sum_duration_sec_30d)/if(recent_days=7,user_count_7d,user_count_30d) as STRING)
from ${APP}.dws_interaction_paper_course_test_exam_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date';
"
# 1.5.3各试卷分数分布统计
ads_paper_score_distribution="
insert overwrite table ${APP}.ads_paper_score_distribution
select * from ${APP}.ads_paper_score_distribution
union
select
    '$do_date' dt,
    1,
    paper_id,
    0_to_20_count,
    20_to_40_count,
    40_to_60_count,
    60_to_80_count,
    80_to_100_count
from ${APP}.dws_interaction_paper_course_test_exam_1d
where dt='$do_date'
union all
select
    '$do_date' dt,
    recent_days,
    paper_id,
    if(recent_days=7,0_to_20_count_7d,0_to_20_count_30d),
    if(recent_days=7,20_to_40_count_7d,20_to_40_count_30d),
    if(recent_days=7,40_to_60_count_7d,40_to_60_count_30d),
    if(recent_days=7,60_to_80_count_7d,60_to_80_count_30d),
    if(recent_days=7,80_to_100_count_7d,80_to_100_count_30d)
from ${APP}.dws_interaction_paper_course_test_exam_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date';
"
# 1.5.4 各题目正确率统计
ads_question_accuracy="
insert overwrite table ${APP}.ads_question_accuracy
select * from ${APP}.ads_question_accuracy
union
select
    '$do_date' dt,
    1,
    question_id,
    answer_count_1d,
    answer_correct_1d,
    cast(answer_correct_1d/answer_count_1d as decimal(16,2))
from ${APP}.dws_interaction_question_test_exam_1d
where dt='$do_date'
union all
select
    '$do_date' dt,
    recent_days,
    question_id,
    if(recent_days=7,answer_count_7d,answer_count_30d),
    if(recent_days=7,answer_correct_7d,answer_correct_30d),
    cast(if(recent_days=7,answer_correct_7d,answer_correct_30d)/if(recent_days=7,answer_count_7d,answer_count_30d) as decimal(16,2))
from ${APP}.dws_interaction_question_test_exam_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date';
"
# 1.6.1 各章节视频播放情况统计
ads_chapter_video_play_stats="
insert overwrite table ${APP}.ads_chapter_video_play_stats
select * from ${APP}.ads_chapter_video_play_stats
union
select
    '$do_date' dt,
    1 recent_days,
    chapter_id,
    video_id,
    sum(play_time_1d),
    cast(sum(sum_play_sec_1d) /count(distinct(user_id)) as decimal(16,2)),
    count(distinct(user_id))
from ${APP}.dws_traffic_user_chapter_video_watch_video_1d
where dt='$do_date'
group by chapter_id,video_id
union
select
    '$do_date' dt,
    recent_days,
    chapter_id,
    video_id,
    sum(if(recent_days=7,play_time_7d,play_time_30d)),
    cast(sum(if(recent_days=7,sum_play_sec_7d,sum_play_sec_30d))/count(distinct(if(recent_days=7,if(play_time_7d=0,null,user_id),user_id))) as decimal(16,2)),
    count(distinct(if(recent_days=7,if(play_time_7d=0,null,user_id),user_id)))
from ${APP}.dws_traffic_user_chapter_video_watch_video_nd
lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date'
group by recent_days,chapter_id,video_id;
"
# 1.6.2 各课程视频播放情况统计
ads_course_video_play_stats="
insert overwrite table ${APP}.ads_course_video_play_stats
select * from ${APP}.ads_course_video_play_stats
union
select
    '$do_date' dt,
    1 recent_days,
    course_id,
    sum(play_time_1d),
    cast(sum(sum_play_sec_1d) /count(distinct(user_id)) as decimal(16,2)),
    count(distinct(user_id))
from ${APP}.dws_traffic_user_chapter_video_watch_video_1d
where dt='$do_date'
group by course_id
union
select
    '$do_date' dt,
    recent_days,
    course_id,
    sum(if(recent_days=7,play_time_7d,play_time_30d)),
    cast(sum(if(recent_days=7,sum_play_sec_7d,sum_play_sec_30d))/count(distinct(if(recent_days=7,if(play_time_7d=0,null,user_id),user_id))) as decimal(16,2)),
    count(distinct(if(recent_days=7,if(play_time_7d=0,null,user_id),user_id)))
from ${APP}.dws_traffic_user_chapter_video_watch_video_nd
lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date'
group by recent_days,course_id;
"
# 1.7.1各课程完课人数统计
ads_course_course_complete_user_count="
insert overwrite table ${APP}.ads_course_course_complete_user_count
select * from ${APP}.ads_course_course_complete_user_count
union
select
    '$do_date' dt,
    1 recent_days,
    course_id,
    count(distinct(user_id))
from
(
    select
        user_id,
        course_id,
        count(distinct(chapter_id)) chapter_complete_count
    from ${APP}.dws_traffic_user_chapter_video_watch_video_1d
    where dt='$do_date' and flag_chapter_complete_1d='1'
    group by user_id,course_id
)complete
left join
(
    select
        id,
        chapter_num
    from ${APP}.dim_course_full
    where dt='$do_date'
)course
on complete.course_id=course.id
where chapter_complete_count=cast(chapter_num as int)
group by course_id
union
select
    '$do_date' dt,
    recent_days,
    course_id,
    count(distinct(user_id))
from
(
    select
        recent_days,
        user_id,
        course_id,
        count(distinct(if(recent_days=7,if(flag_chapter_complete_7d=1,chapter_id,null),chapter_id))) chapter_complete_count
    from ${APP}.dws_traffic_user_chapter_video_watch_video_nd
    lateral view explode(array(7,30)) tmp as recent_days
    where dt='$do_date' and flag_chapter_complete_30d='1'
    group by recent_days,user_id,course_id
)complete
left join
(
    select
        id,
        chapter_num
    from ${APP}.dim_course_full
    where dt='$do_date'
)course
on complete.course_id=course.id
where chapter_complete_count=cast(chapter_num as int)
group by recent_days,course_id;
"
# 1.7.2 完课综合指标
ads_course_complete_stats="
insert overwrite table ${APP}.ads_course_complete_stats
select * from ${APP}.ads_course_complete_stats
union
select
    '$do_date' dt,
    1 recent_days,
    count(distinct(user_id)),
    count(user_id)
from
(
    select
        user_id,
        course_id,
        count(distinct(chapter_id)) chapter_complete_count
    from ${APP}.dws_traffic_user_chapter_video_watch_video_1d
    where dt='$do_date' and flag_chapter_complete_1d='1'
    group by user_id,course_id
)complete
left join
(
    select
        id,
        chapter_num
    from ${APP}.dim_course_full
    where dt='$do_date'
)course
on complete.course_id=course.id
where chapter_complete_count=cast(chapter_num as int)
union
select
    '$do_date' dt,
    recent_days,
    count(distinct(user_id)),
    count(user_id)
from
(
    select
        recent_days,
        user_id,
        course_id,
        count(distinct(if(recent_days=7,if(flag_chapter_complete_7d=1,chapter_id,null),chapter_id))) chapter_complete_count
    from ${APP}.dws_traffic_user_chapter_video_watch_video_nd
    lateral view explode(array(7,30)) tmp as recent_days
    where dt='$do_date' and flag_chapter_complete_30d='1'
    group by recent_days,user_id,course_id
)complete
left join
(
    select
        id,
        chapter_num
    from ${APP}.dim_course_full
    where dt='$do_date'
)course
on complete.course_id=course.id
where chapter_complete_count=cast(chapter_num as int)
group by recent_days;
"
# 1.7.3 各课程人均完成章节视频数统计
ads_course_chapter_complete_user_average_count="
insert overwrite table ${APP}.ads_course_chapter_complete_user_average_count
select * from ${APP}.ads_course_chapter_complete_user_average_count
union
select
    '$do_date' dt,
    1 recent_days,
    course_id,
    cast(count(*)/count(distinct(user_id)) as decimal(16,2)) complete_user_average_count
from ${APP}.dws_traffic_user_chapter_video_watch_video_1d
where dt='$do_date' and flag_chapter_complete_1d='1'
group by course_id
union
select
    '$do_date' dt,
    recent_days,
    course_id,
    cast(count(if(recent_days=7,if(flag_chapter_complete_7d=1,chapter_id,null),chapter_id))
             /count(distinct(if(recent_days=7,if(flag_chapter_complete_7d=1,user_id,null),user_id))) as decimal(16,2))
from ${APP}.dws_traffic_user_chapter_video_watch_video_nd
lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date' and flag_chapter_complete_30d='1'
group by recent_days,course_id;
"

case $1 in
    "ads_traffic_stats_by_channel" )
        hive -e "$ads_traffic_stats_by_channel"
    ;;
    "ads_page_path" )
        hive -e "$ads_page_path"
    ;;
    "ads_sc_order_stats" )
        hive -e "$ads_sc_order_stats"
    ;;
    "ads_user_change" )
        hive -e "$ads_user_change"
    ;;
    "ads_user_retention" )
        hive -e "$ads_user_retention"
    ;;
    "ads_user_stats" )
        hive -e "$ads_user_stats"
    ;;
    "ads_user_action" )
        hive -e "$ads_user_action"
    ;;
    "ads_new_buyer_stats" )
        hive -e "$ads_new_buyer_stats"
    ;;
    "ads_age_range_order_stats" )
        hive -e "$ads_age_range_order_stats"
    ;;
    "ads_trade_stats_by_cate" )
        hive -e "$ads_trade_stats_by_cate"
    ;;
    "ads_trade_stats_by_sub" )
        hive -e "$ads_trade_stats_by_sub"
    ;;
    "ads_trade_stats_by_course" )
        hive -e "$ads_trade_stats_by_course"
    ;;
    "ads_course_review_stats" )
        hive -e "$ads_course_review_stats"
    ;;
    "ads_audition_category_retention" )
        hive -e "$ads_audition_category_retention"
    ;;
    "ads_audition_subject_retention" )
        hive -e "$ads_audition_subject_retention"
    ;;
    "ads_audition_course_retention" )
        hive -e "$ads_audition_course_retention"
    ;;
    "ads_trade_stats" )
        hive -e "$ads_trade_stats"
    ;;
    "ads_order_by_province" )
        hive -e "$ads_order_by_province"
    ;;
    "ads_paper" )
        hive -e "$ads_paper"
    ;;
    "ads_course_exam" )
        hive -e "$ads_course_exam"
    ;;
    "ads_paper_score_distribution" )
        hive -e "$ads_paper_score_distribution"
    ;;
    "ads_question_accuracy" )
        hive -e "$ads_question_accuracy"
    ;;
    "ads_chapter_video_play_stats" )
        hive -e "$ads_chapter_video_play_stats"
    ;;
    "ads_course_video_play_stats" )
        hive -e "$ads_course_video_play_stats"
    ;;
    "ads_course_course_complete_user_count" )
        hive -e "$ads_course_course_complete_user_count"
    ;;
    "ads_course_complete_stats" )
        hive -e "$ads_course_complete_stats"
    ;;
    "ads_course_chapter_complete_user_average_count" )
        hive -e "$ads_course_chapter_complete_user_average_count"
    ;;
    "all" )
        hive -e "$ads_traffic_stats_by_channel$ads_page_path$ads_sc_order_stats$ads_user_change$ads_user_retention$ads_user_stats$ads_user_action$ads_new_buyer_stats$ads_age_range_order_stats$ads_trade_stats_by_cate$ads_trade_stats_by_sub$ads_trade_stats_by_course$ads_course_review_stats$ads_audition_category_retention$ads_audition_subject_retention$ads_audition_course_retention$ads_trade_stats$ads_order_by_province$ads_paper$ads_course_exam$ads_paper_score_distribution$ads_question_accuracy$ads_chapter_video_play_stats$ads_course_video_play_stats$ads_course_course_complete_user_count$ads_course_complete_stats$ads_course_chapter_complete_user_average_count"
    ;;
esac
